<?php
namespace app\controllers\crontab;
use app\components\EmailMarket;
use app\components\redis\RedisMarketingEmail;
use app\components\redis\RedisLock;
use Yii;
class DealCategoryDataController extends \app\base\Controller 
{
    protected $redisGroup;  
    protected $gbCategoryCh = [
        'MobilePhones'=>1,
        'TabletPCAccessories'=>2,
        'ConsumerElectronics'=>3,
        'ComputersNetworking'=>4,
        'ToysHobbies'=>5,
        'HomeGarden'=>6,
        'LEDLightsFlashlights'=>7,
        'Outdoorsports'=>8,
        'ElectricalTools'=>9,
        'AutomobilesMotorcycle'=>10,
        'OfficeschoolSupplies'=>11,
        'Appliances'=>12,
        'HealthBeauty'=>13,
        'BabyKids'=>14,
        'ElectronicCigarettes'=>15,
    ];
    protected $lrCountryToLogogram = [ // 特殊几个国家的数据需要转换
        'AZERBAIJAN' => "Azerbaijan Republic",
        'HONG KONG' => "Hongkong",
        'RUSSIA' => "RUSSIAN FEDERATION",
        'SLOVAKIA REPUBLIC' => "Slovakia",
        'Tahiti'=> "France",
    ];
    
    private function getRedis($site)
    {
        if (!empty(Yii::$app->params['sites']['marketing_email']['web_config'][$site]['redisGroup'])) {
            $this->redisGroup = Yii::$app->params['sites']['marketing_email']['web_config'][$site]['redisGroup'];
        } elseif (!empty(Yii::$app->params['sites']['marketing_email']['redisGroup'])) {
            $this->redisGroup = Yii::$app->params['sites']['marketing_email']['redisGroup'];
        } else {
            $this->redisGroup = 'cache'; 
        }
    }
    
    /**
     * 远程邮箱解密测试
     */
    public function actionDecryptionEmail()
    {
        $apiUrl = [
            'dev' => 'http://oms.hqygou.com/interface/emp/getemail',
            'test' => 'http://oms.hqygou.com/interface/emp/getemail',
            'product' => 'http://oms.gw-ec.com/interface/emp/getemail',
        ];
        
        // 访问密钥
        $apiToken = [
            'dev' => 'jowEf4hih56A89xcB',
            'test' => 'jowEf4hih56A89xcB',
            'product' => 'jowEf4hih56A89xcB',
        ];
        
        // 解密密钥
        $decryptionToken = [
            'dev' => 'jowEf4hih56A89xcB',
            'test' => 'jowEf4hih56A89xcB',
            'product' => 'jowEf4hih56A89xcB',
        ];
        
        
         $emailMarket = new EmailMarket($decryptionToken[YII_ENV]);
        //$testEmail = "815227173@qq.com";
        //echo $testEmail.'<br>';
        //$jiamiEmail = $emailMarket->encryption($testEmail);
        //echo $jiamiEmail.'<br>';
        //$jiemiEmail = $emailMarket->decryption($jiamiEmail);
        //echo $jiemiEmail;
       // exit;
        // $idsArr = array(20821462,20894078,20894135);
        $idsArr = array(50488593,50488577,50488564);
        $ids = json_encode($idsArr);
        $key = md5($apiToken[YII_ENV].$ids);
	
        $params = array(
		'key'  => $key,
		'data' => $ids
        );
	
        $data = json_encode($params);
	
        $url = $apiUrl[YII_ENV];

        $ch = curl_init();
        
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        
        $response = curl_exec($ch);
        
        curl_close($ch);
        
        $result = json_decode($response, true);
        
        foreach ($result['data'] as $emailSecret) {
            $email = $emailMarket->decryption($emailSecret);
            echo $email;
            echo '<br>';
        }
        echo '<pre>';
        var_dump($result);
        exit;
    }
    
    private function getDecryptionEmail($idsArr)
    {
        // 解密邮箱url地址
        $apiUrl = [
            'dev' => 'http://oms.hqygou.com/interface/emp/getemail',
            'test' => 'http://oms.hqygou.com/interface/emp/getemail',
            'product' => 'http://oms.gw-ec.com/interface/emp/getemail',
        ];
        
        // 访问密钥
        $apiToken = [
            'dev' => 'jowEf4hih56A89xcB',
            'test' => 'jowEf4hih56A89xcB',
            'product' => 'jowEf4hih56A89xcB',
        ];
        
        // 解密密钥
        $decryptionToken = [
            'dev' => 'jowEf4hih56A89xcB',
            'test' => 'jowEf4hih56A89xcB',
            'product' => 'jowEf4hih56A89xcB',
        ];
        
        // 保存解密要的邮箱
        $decryptionEmail = [];
        
        $emailMarket = new EmailMarket($decryptionToken[YII_ENV]);
        $ids = json_encode($idsArr);
        $key = md5($apiToken[YII_ENV].$ids);
	
        $params = array(
            'key'  => $key,
            'data' => $ids
        );
	
        $data = json_encode($params);
	
        $url = $apiUrl[YII_ENV];

        $ch = curl_init();
        
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        
        $response = curl_exec($ch);
        
        curl_close($ch);
        
        $result = json_decode($response, true);
        
        foreach ($result['data'] as $userId=>$emailSecret) {
            $decryptionEmail[$userId] = $emailMarket->decryption($emailSecret);
        }
        return $decryptionEmail;
    }
   
    /**
     * 获取LR站用户品类数据脚本（自动获取版）
     */
    public function actionGetLrDataAuto()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $oneTimeNums = 5000; // 每次获取的数据条数 
        $redis = new RedisMarketingEmail();
        $redisLock = new RedisLock($redis, "get_user_category_data_langria", 600);
        if ($redisLock->translock() === false) {
            $msg = "当前lR站已经有一个进程在获取数据";
            Elog::logCrontab($msg,__FUNCTION__,'info');
            return 0;
        }
        
        // 保存上一次获取的id值
        $redisLastIdKey = "langria:getlastid"; 
        
        // 保存最后一次取到数据的时间
        $redisLastGetDataTime = "langria:lastgettime";
        
        $curTime = time();
        
        $lastGetDataTime = $redis->get($redisLastGetDataTime);
        $lastGetDataTime = empty($lastGetDataTime) ? 0 : intval($lastGetDataTime);
        if ($curTime - $lastGetDataTime > 3*24*3600) { // 三天没有重新取数据的话就要重新再取数据
            $redis->set($redisLastIdKey,0);
        }
        $emailMarket = new EmailMarket();
        $lastId = $redis->get($redisLastIdKey);
        $lastId = empty($lastId) ? 0 : intval($lastId);
        
        $selectSql = " select * from public_dm_oms_emp_goods where id > {$lastId} order by id asc limit 5000 ";
        $dataInfo = Yii::$app->lrcategorydb->createCommand($selectSql)->queryAll();
        
        // 国家编码数据
        $userCountryCodeArr = [];
        $userCountryBriefCodeArr = [];
        $userCountryBriefCodeArrTmp = [];
        $userCountryCodeStr = "";
        // 用户id数据
        $siteUserIdArr = [];
        $siteUserIdStr = "";
        $insertArr=[];
        $insertArrCategoryId = [];

        $updateArr = [];
        $updateArrCategoryId = [];
        // 遍历获取网站用户id地址
        if(!empty($dataInfo)) {
            $dataCount = count($dataInfo);
            $maxDataId = $dataInfo[$dataCount-1]['id'];
            $redis->set($redisLastIdKey,$maxDataId);
            $redis->set($redisLastGetDataTime,$curTime);
            foreach ($dataInfo as $dataInfoKey=>$dataVal) {
                $siteUserIdArr[] = $dataVal['customer_id'];
                if (!empty($this->lrCountryToLogogram[$dataVal['country_id']])) {
                    // $userCountryCodeArr[$dataInfoKey]['country'] = $this->lrCountryToLogogram[$dataVal['country_id']];
                    $dataInfo[$dataInfoKey]['country_id'] = $this->lrCountryToLogogram[$dataVal['country_id']];

                } 

                $userCountryCodeArr[] = $dataInfo[$dataInfoKey]['country_id'];
                // else {
                    // $userCountryCodeArr[$dataInfoKey]['country'] = $dataVal['country_id'];
                // }

            }
        } 

        if (!empty($userCountryCodeArr)) {
            $userCountryCodeStr = join("','",$userCountryCodeArr);

            $getBriefCodeSql = "select region_code,region_name from region where region_name in ('{$userCountryCodeStr}')";
            $userCountryBriefCodeArrTmp = Yii::$app->db->createCommand($getBriefCodeSql)->queryAll();

            foreach ($userCountryBriefCodeArrTmp as $userCountryBriefCodeArrTmpVal) {

                $userCountryBriefCodeArr[$userCountryBriefCodeArrTmpVal['region_name']] = $userCountryBriefCodeArrTmpVal['region_code'];
            }

        }

        $existUserIdArrDeal = [];
        $userEmailArr = [];
        if (!empty($siteUserIdArr) && is_array($siteUserIdArr)) {
            $siteUserIdStr = join(',', $siteUserIdArr);
            $selectUserSql = " select user_id,site_user_id from user_langria where site_user_id in ({$siteUserIdStr})";
            $existUserIdArrTemp =  Yii::$app->db->createCommand($selectUserSql)->queryAll();
            foreach ($existUserIdArrTemp as $existUserIdArrTempVal) { 
                $existUserIdArrDeal[$existUserIdArrTempVal['site_user_id']] = $existUserIdArrTempVal['user_id'];
            }

            // 远程解密
            $existUserEmailTemp = $this->getDecryptionEmail($siteUserIdArr);


        }

       
        foreach ($dataInfo as $dataVal) {

            $dataVal['sales_time'] = \yii::$app->bjTime->strtotime($dataVal['sales_time']);
            // 当前用户如果存在则更新数据不存在则插入数据
            if (!empty($existUserIdArrDeal[$dataVal['customer_id']])) {

                if (empty($existUserEmailTemp[$dataVal['customer_id']])) {
                    continue;
                }
                // 更新相关数据
                $updateArr[$dataVal['customer_id']]['sales_time'] = $dataVal['sales_time'];  
                $updateArr[$dataVal['customer_id']]['order_count'] = $dataVal['order_count'];  
                $updateArr[$dataVal['customer_id']]['order_total_price'] = $dataVal['order_total_price'];
                $updateArr[$dataVal['customer_id']]['country_id'] =  $dataVal['country_id'];

                $matchArrDomain = [];
                // 获取截取邮箱地址中的域名
                preg_match('/@([^@]*?)$/',$existUserEmailTemp[$dataVal['customer_id']],$matchArrDomain);
                if (empty($matchArrDomain[1])) {
                    $matchArrDomain[1] = '';
                }
                $emailType = 1;
                $emailType = $emailMarket->getEmailType($existUserEmailTemp[$dataVal['customer_id']]);
                $updateArr[$dataVal['customer_id']]['email_type'] = $emailType; 
                $updateArr[$dataVal['customer_id']]['email_domain'] = $matchArrDomain[1];

                // 处理相关分类数据（把分类路径的数据也加上）
                $categorySql = "select path,level from lr_category where id={$dataVal['cat_id']}";
                $curPath = Yii::$app->db->createCommand($categorySql)->queryOne();
                if (!empty($curPath['path'])) {
                    $catePathArr = explode(',',$curPath['path']);

                     // 说明这些id的有相关数据
                    $updateLrCategoryHaveUserSql = " update lr_category set have_user = 1 where id in ({$curPath['path']}) ";
                    Yii::$app->db->createCommand($updateLrCategoryHaveUserSql)->execute();
                    // 改成所有的分类级别信息都保存下来
                    unset($catePathArr[0]);
                    $catePathArrDeal = [];
                    $catePath = "";
                    for ($i=1;$i<=$curPath['level'];$i++) {
                        $catePathArrTmp = [];
                        for($j=1;$j<=$i;$j++) {
                            $catePathArrTmp[] = $catePathArr[$j];
                        }

                        $updateArrCategoryId[$dataVal['customer_id']][] = '/'.join('/',$catePathArrTmp).'/';
                    }

                } else {
                    $updateArrCategoryId[$dataVal['customer_id']][] = $dataVal['cat_id'];
                }
            } else {

                // 处理相关分类数据（把分类路径的数据也加上）
                $categorySql = "select path,level from lr_category where id={$dataVal['cat_id']}";
                $curPath = Yii::$app->db->createCommand($categorySql)->queryOne();
                if (!empty($curPath['path'])) {
                    $catePathArr = explode(',',$curPath['path']);

                    // 说明这些id的有相关数据
                    $updateLrCategoryHaveUserSql = " update lr_category set have_user = 1 where id in ({$curPath['path']}) ";
                    Yii::$app->db->createCommand($updateLrCategoryHaveUserSql)->execute();
                    // 改成所有的分类级别信息都保存下来
                    unset($catePathArr[0]);

                    $catePathArrDeal = [];
                    $catePath = "";


                    for ($i=1;$i<=$curPath['level'];$i++) {
                        $catePathArrTmp = [];
                        for($j=1;$j<=$i;$j++) {
                            $catePathArrTmp[] = $catePathArr[$j];
                        }
                        $insertArrCategoryId[$dataVal['customer_id']][] = '/'.join('/',$catePathArrTmp).'/';
                    }


                } else {
                    $insertArrCategoryId[$dataVal['customer_id']][] = '/'.$dataVal['cat_id'].'/';
                }

                // 插入数据
                if (!empty($existUserEmailTemp[$dataVal['customer_id']])) {

                    if (!empty($userCountryBriefCodeArr[trim($dataVal['country_id'])])) {
                        $regionCode = $userCountryBriefCodeArr[trim($dataVal['country_id'])];
                    } else {
                        $regionCode = '';
                    }

                    $matchArrDomain = [];
                    // 获取截取邮箱地址中的域名
                    preg_match('/@([^@]*?)$/',$existUserEmailTemp[$dataVal['customer_id']],$matchArrDomain);
                    if (empty($matchArrDomain[1])) {
                        $matchArrDomain[1] = '';
                    }
                    $emailType = 1;
                    $emailType = $emailMarket->getEmailType($existUserEmailTemp[$dataVal['customer_id']]);

                    // 邮箱再次加密
                    $emailSecret = $emailMarket->encryption($existUserEmailTemp[$dataVal['customer_id']]);
                    $curTimeInt = time();

                    $insertArr[$dataVal['customer_id']] = "'{$regionCode}',1,'".$emailSecret."',"."'{$matchArrDomain[1]}'".",{$emailType},{$curTimeInt},{$curTimeInt},".$dataVal['customer_id'].",'".$dataVal['sales_time']."',".$dataVal['order_count'].",".$dataVal['order_total_price'];
                }
                // else {
                    // $insertArr[$dataVal['customer_id']] = "1,'".$dataVal['customer_email']."',"."''".",".$dataVal['customer_id'].",'".$dataVal['sales_time']."',".$dataVal['order_count'].",".$dataVal['order_total_price'];
                // }


            }

        }

        if (!empty($updateArr)) {
            foreach ($updateArr as $updateArrKey=>$updateArrVal) {


                if (!empty($updateArrCategoryId[$updateArrKey])) {
                    $updateCategoryStr = ",".join(',',$updateArrCategoryId[$updateArrKey]).",";
                } else {
                    $updateCategoryStr = "";
                }
                if (!empty($userCountryBriefCodeArr[trim($updateArrVal['country_id'])])) {
                    $regionCode = $userCountryBriefCodeArr[trim($updateArrVal['country_id'])];
                } else {
                    $regionCode = '';
                }
                $curTimeInt = time();


                $updateSql = "update user_langria set reg_time=1, last_order_time='{$updateArrVal['sales_time']}',order_count={$updateArrVal['order_count']},order_total_price={$updateArrVal['order_total_price']},category_id_str='{$updateCategoryStr}',region_code='{$regionCode}',email_domain='{$updateArrVal['email_domain']}',email_type={$updateArrVal['email_type']},update_time={$curTimeInt} where site_user_id = {$updateArrKey}";

                Yii::$app->db->createCommand($updateSql)->execute();
            }
        }

        if (!empty($insertArr)) {
            foreach ($insertArr as $insertArrKey=>$insertArrVal) {
                if (!empty($insertArrCategoryId[$insertArrKey])) {
                    $insertArr[$insertArrKey] .=",',".join(',',$insertArrCategoryId[$insertArrKey]).",'";
                } else {
                    $insertArr[$insertArrKey] .= ",''";
                } 
            }

            $insertSql = $insertPre."(".join("),(",$insertArr).")";   

            Yii::$app->db->createCommand($insertSql)->execute();
        }
        $redisLock->unlock();
    }
    
    /**
     * 获取LR站品类相关数据(新版)（邮箱根据接口解密） 
     */
    public function actionDealLrDataNew()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $emailMarket = new EmailMarket();
        if (!empty($_GET['id'])) {
            $fromId = $_GET['id'];
        } else {
            $fromId = 0;
        }
      
        $sql = "select count(*) from public_dm_oms_emp_goods where id > {$fromId}"; 
        $dataCount =  Yii::$app->lrcategorydb->createCommand($sql)->queryScalar();
        // 每次处理5000条
        $times = intval(ceil($dataCount / 5000));
        $insertPre = "insert into user_langria(region_code,reg_time,email,email_domain,email_type,add_date,update_time,site_user_id,last_order_time,order_count,order_total_price,category_id_str) values ";
        $insertSql = "";
        for ($i=1;$i<=$times;$i++) {
            $selectSql = " select * from public_dm_oms_emp_goods where id > {$fromId} order by id asc limit 5000 ";
            $dataInfo = Yii::$app->lrcategorydb->createCommand($selectSql)->queryAll();
            
            // 国家编码数据
            $userCountryCodeArr = [];
            $userCountryBriefCodeArr = [];
            $userCountryBriefCodeArrTmp = [];
            $userCountryCodeStr = "";
            // 用户id数据
            $siteUserIdArr = [];
            $siteUserIdStr = "";
            $insertArr=[];
            $insertArrCategoryId = [];
            
            $updateArr = [];
            $updateArrCategoryId = [];
            // 遍历获取网站用户id地址
            if(!empty($dataInfo)) {
                foreach ($dataInfo as $dataInfoKey=>$dataVal) {
                    $siteUserIdArr[] = $dataVal['customer_id'];
                    if (!empty($this->lrCountryToLogogram[$dataVal['country_id']])) {
                        // $userCountryCodeArr[$dataInfoKey]['country'] = $this->lrCountryToLogogram[$dataVal['country_id']];
                        $dataInfo[$dataInfoKey]['country_id'] = $this->lrCountryToLogogram[$dataVal['country_id']];

                    } 

                    $userCountryCodeArr[] = $dataInfo[$dataInfoKey]['country_id'];
                    // else {
                        // $userCountryCodeArr[$dataInfoKey]['country'] = $dataVal['country_id'];
                    // }

                }
            }
            
            if (!empty($userCountryCodeArr)) {
                $userCountryCodeStr = join("','",$userCountryCodeArr);
                
                $getBriefCodeSql = "select region_code,region_name from region where region_name in ('{$userCountryCodeStr}')";
                $userCountryBriefCodeArrTmp = Yii::$app->db->createCommand($getBriefCodeSql)->queryAll();
                
                foreach ($userCountryBriefCodeArrTmp as $userCountryBriefCodeArrTmpVal) {
                    
                    $userCountryBriefCodeArr[$userCountryBriefCodeArrTmpVal['region_name']] = $userCountryBriefCodeArrTmpVal['region_code'];
                }
                
            }
            
            $existUserIdArrDeal = [];
            $userEmailArr = [];
            if (!empty($siteUserIdArr) && is_array($siteUserIdArr)) {
                $siteUserIdStr = join(',', $siteUserIdArr);
                $selectUserSql = " select user_id,site_user_id from user_langria where site_user_id in ({$siteUserIdStr})";
                $existUserIdArrTemp =  Yii::$app->db->createCommand($selectUserSql)->queryAll();
                foreach ($existUserIdArrTemp as $existUserIdArrTempVal) { 
                    $existUserIdArrDeal[$existUserIdArrTempVal['site_user_id']] = $existUserIdArrTempVal['user_id'];
                }
                
                // 远程解密
                $existUserEmailTemp = $this->getDecryptionEmail($siteUserIdArr);
                
               
            }
            
            if(!empty($dataInfo))
            foreach ($dataInfo as $dataVal) {
                
                $dataVal['sales_time'] = \yii::$app->bjTime->strtotime($dataVal['sales_time']);
                // 当前用户如果存在则更新数据不存在则插入数据
                if (!empty($existUserIdArrDeal[$dataVal['customer_id']])) {
                    
                    if (empty($existUserEmailTemp[$dataVal['customer_id']])) {
                        continue;
                    }
                    // 更新相关数据
                    $updateArr[$dataVal['customer_id']]['sales_time'] = $dataVal['sales_time'];  
                    $updateArr[$dataVal['customer_id']]['order_count'] = $dataVal['order_count'];  
                    $updateArr[$dataVal['customer_id']]['order_total_price'] = $dataVal['order_total_price'];
                    $updateArr[$dataVal['customer_id']]['country_id'] =  $dataVal['country_id'];
                    
                    $matchArrDomain = [];
                    // 获取截取邮箱地址中的域名
                    preg_match('/@([^@]*?)$/',$existUserEmailTemp[$dataVal['customer_id']],$matchArrDomain);
                    if (empty($matchArrDomain[1])) {
                        $matchArrDomain[1] = '';
                    }
                    $emailType = 1;
                    $emailType = $emailMarket->getEmailType($existUserEmailTemp[$dataVal['customer_id']]);
                    $updateArr[$dataVal['customer_id']]['email_type'] = $emailType; 
                    $updateArr[$dataVal['customer_id']]['email_domain'] = $matchArrDomain[1];
                    
                    // 处理相关分类数据（把分类路径的数据也加上）
                    $categorySql = "select path,level from lr_category where id={$dataVal['cat_id']}";
                    $curPath = Yii::$app->db->createCommand($categorySql)->queryOne();
                    if (!empty($curPath['path'])) {
                        $catePathArr = explode(',',$curPath['path']);
                        
                         // 说明这些id的有相关数据
                        $updateLrCategoryHaveUserSql = " update lr_category set have_user = 1 where id in ({$curPath['path']}) ";
                        Yii::$app->db->createCommand($updateLrCategoryHaveUserSql)->execute();
                        // 改成所有的分类级别信息都保存下来
                        unset($catePathArr[0]);
                        $catePathArrDeal = [];
                        $catePath = "";
                        for ($i=1;$i<=$curPath['level'];$i++) {
                            $catePathArrTmp = [];
                            for($j=1;$j<=$i;$j++) {
                                $catePathArrTmp[] = $catePathArr[$j];
                            }
                          
                            $updateArrCategoryId[$dataVal['customer_id']][] = '/'.join('/',$catePathArrTmp).'/';
                        }
                       
                    } else {
                        $updateArrCategoryId[$dataVal['customer_id']][] = $dataVal['cat_id'];
                    }
                } else {
                    
                    // 处理相关分类数据（把分类路径的数据也加上）
                    $categorySql = "select path,level from lr_category where id={$dataVal['cat_id']}";
                    $curPath = Yii::$app->db->createCommand($categorySql)->queryOne();
                    if (!empty($curPath['path'])) {
                        $catePathArr = explode(',',$curPath['path']);
                        
                        // 说明这些id的有相关数据
                        $updateLrCategoryHaveUserSql = " update lr_category set have_user = 1 where id in ({$curPath['path']}) ";
                        Yii::$app->db->createCommand($updateLrCategoryHaveUserSql)->execute();
                        // 改成所有的分类级别信息都保存下来
                        unset($catePathArr[0]);
                        
                        $catePathArrDeal = [];
                        $catePath = "";
                       
                       
                        for ($i=1;$i<=$curPath['level'];$i++) {
                            $catePathArrTmp = [];
                            for($j=1;$j<=$i;$j++) {
                                $catePathArrTmp[] = $catePathArr[$j];
                            }
                            $insertArrCategoryId[$dataVal['customer_id']][] = '/'.join('/',$catePathArrTmp).'/';
                        }
                       
                       
                    } else {
                        $insertArrCategoryId[$dataVal['customer_id']][] = '/'.$dataVal['cat_id'].'/';
                    }
                    
                    // 插入数据
                    if (!empty($existUserEmailTemp[$dataVal['customer_id']])) {
                       
                        if (!empty($userCountryBriefCodeArr[trim($dataVal['country_id'])])) {
                            $regionCode = $userCountryBriefCodeArr[trim($dataVal['country_id'])];
                        } else {
                            $regionCode = '';
                        }
                        
                        $matchArrDomain = [];
                        // 获取截取邮箱地址中的域名
                        preg_match('/@([^@]*?)$/',$existUserEmailTemp[$dataVal['customer_id']],$matchArrDomain);
                        if (empty($matchArrDomain[1])) {
                            $matchArrDomain[1] = '';
                        }
                        $emailType = 1;
                        $emailType = $emailMarket->getEmailType($existUserEmailTemp[$dataVal['customer_id']]);
                        
                        // 邮箱再次加密
                        $emailSecret = $emailMarket->encryption($existUserEmailTemp[$dataVal['customer_id']]);
                        $curTimeInt = time();
                        
                        $insertArr[$dataVal['customer_id']] = "'{$regionCode}',1,'".$emailSecret."',"."'{$matchArrDomain[1]}'".",{$emailType},{$curTimeInt},{$curTimeInt},".$dataVal['customer_id'].",'".$dataVal['sales_time']."',".$dataVal['order_count'].",".$dataVal['order_total_price'];
                    }
                    // else {
                        // $insertArr[$dataVal['customer_id']] = "1,'".$dataVal['customer_email']."',"."''".",".$dataVal['customer_id'].",'".$dataVal['sales_time']."',".$dataVal['order_count'].",".$dataVal['order_total_price'];
                    // }
                    
                    
                }
                
            }
           
            if (!empty($updateArr)) {
                foreach ($updateArr as $updateArrKey=>$updateArrVal) {
                    
                   
                    if (!empty($updateArrCategoryId[$updateArrKey])) {
                        $updateCategoryStr = ",".join(',',$updateArrCategoryId[$updateArrKey]).",";
                    } else {
                        $updateCategoryStr = "";
                    }
                    if (!empty($userCountryBriefCodeArr[trim($updateArrVal['country_id'])])) {
                        $regionCode = $userCountryBriefCodeArr[trim($updateArrVal['country_id'])];
                    } else {
                        $regionCode = '';
                    }
                    $curTimeInt = time();
                   
                   
                    $updateSql = "update user_langria set reg_time=1, last_order_time='{$updateArrVal['sales_time']}',order_count={$updateArrVal['order_count']},order_total_price={$updateArrVal['order_total_price']},category_id_str='{$updateCategoryStr}',region_code='{$regionCode}',email_domain='{$updateArrVal['email_domain']}',email_type={$updateArrVal['email_type']},update_time={$curTimeInt} where site_user_id = {$updateArrKey}";
                   
                    Yii::$app->db->createCommand($updateSql)->execute();
                }
            }
            
            if (!empty($insertArr)) {
                foreach ($insertArr as $insertArrKey=>$insertArrVal) {
                    if (!empty($insertArrCategoryId[$insertArrKey])) {
                        $insertArr[$insertArrKey] .=",',".join(',',$insertArrCategoryId[$insertArrKey]).",'";
                    } else {
                        $insertArr[$insertArrKey] .= ",''";
                    } 
                }
                
                $insertSql = $insertPre."(".join("),(",$insertArr).")";   
               
                Yii::$app->db->createCommand($insertSql)->execute();
            }
            sleep(10);
        } 
    }
    
    /**
     * 获取LR站品类相关数据
     */
   
    public function actionDealLrData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $emailMarket = new EmailMarket();
        if (!empty($_GET['id'])) {
            $fromId = $_GET['id'];
        } else {
            $fromId = 0;
        }
      
        $sql = "select count(*) from public_dm_oms_emp_goods where id > {$fromId}"; 
        $dataCount =  Yii::$app->lrcategorydb->createCommand($sql)->queryScalar();
        // 每次处理5000条
        $times = intval(ceil($dataCount / 5000));
        $insertPre = "insert into user_langria(region_code,reg_time,email,email_domain,site_user_id,last_order_time,order_count,order_total_price,category_id_str) values ";
        $insertSql = "";
        for ($i=1;$i<=$times;$i++) {
            $selectSql = " select * from public_dm_oms_emp_goods where id > {$fromId} order by id asc limit 5000 ";
            $dataInfo = Yii::$app->lrcategorydb->createCommand($selectSql)->queryAll();
            
            // 国家编码数据
            $userCountryCodeArr = [];
            $userCountryBriefCodeArr = [];
            $userCountryBriefCodeArrTmp = [];
            $userCountryCodeStr = "";
            // 用户id数据
            $siteUserIdArr = [];
            $siteUserIdStr = "";
            $insertArr=[];
            $insertArrCategoryId = [];
            
            $updateArr = [];
            $updateArrCategoryId = [];
            // 遍历获取网站用户id地址       
            foreach ($dataInfo as $dataInfoKey=>$dataVal) {
                $siteUserIdArr[] = $dataVal['customer_id'];
                if (!empty($this->lrCountryToLogogram[$dataVal['country_id']])) {
                    // $userCountryCodeArr[$dataInfoKey]['country'] = $this->lrCountryToLogogram[$dataVal['country_id']];
                    $dataInfo[$dataInfoKey]['country_id'] = $this->lrCountryToLogogram[$dataVal['country_id']];
                    
                } 
                
                $userCountryCodeArr[] = $dataInfo[$dataInfoKey]['country_id'];
                // else {
                    // $userCountryCodeArr[$dataInfoKey]['country'] = $dataVal['country_id'];
                // }
                
            }
            
            if (!empty($userCountryCodeArr)) {
                $userCountryCodeStr = join("','",$userCountryCodeArr);
                
                $getBriefCodeSql = "select region_code,region_name from region where region_name in ('{$userCountryCodeStr}')";
                $userCountryBriefCodeArrTmp = Yii::$app->db->createCommand($getBriefCodeSql)->queryAll();
                
                foreach ($userCountryBriefCodeArrTmp as $userCountryBriefCodeArrTmpVal) {
                    
                    $userCountryBriefCodeArr[$userCountryBriefCodeArrTmpVal['region_name']] = $userCountryBriefCodeArrTmpVal['region_code'];
                }
                
            }
            
            $existUserIdArrDeal = [];
            $userEmailArr = [];
            if (!empty($siteUserIdArr) && is_array($siteUserIdArr)) {
                $siteUserIdStr = join(',', $siteUserIdArr);
                
                $selectUserSql = " select user_id,site_user_id from user_langria where site_user_id in ({$siteUserIdStr})";
                $existUserIdArrTemp =  Yii::$app->db->createCommand($selectUserSql)->queryAll();
                
                foreach ($existUserIdArrTemp as $existUserIdArrTempVal) { 
                    $existUserIdArrDeal[$existUserIdArrTempVal['site_user_id']] = $existUserIdArrTempVal['user_id'];
                }
                
                // 查询获取相关用户邮箱
                $selectUserEmailSql = " select site_user_id,email from lr_email_tmp where site_user_id in ({$siteUserIdStr})";
                $existUserEmailTemp =  Yii::$app->db->createCommand($selectUserEmailSql)->queryAll();
                
                foreach ($existUserEmailTemp as $existUserEmailTempVal) {
                    if (!empty($existUserEmailTempVal['email'])) {
                        $existUserEmailTemp[$existUserEmailTempVal['site_user_id']] =  $emailMarket->encryption($existUserEmailTempVal['email']);
                    }
                    
                }
            }
            
            foreach ($dataInfo as $dataVal) {
                
                 $dataVal['sales_time'] = \yii::$app->bjTime->strtotime($dataVal['sales_time']);
                // 当前用户如果存在则更新数据不存在则插入数据
                if (!empty($existUserIdArrDeal[$dataVal['customer_id']])) {
                    
                    // 更新相关数据
                    $updateArr[$dataVal['customer_id']]['sales_time'] = $dataVal['sales_time'];  
                    $updateArr[$dataVal['customer_id']]['order_count'] = $dataVal['order_count'];  
                    $updateArr[$dataVal['customer_id']]['order_total_price'] = $dataVal['order_total_price'];
                     $updateArr[$dataVal['customer_id']]['country_id'] =  $dataVal['country_id'];
                    
                    // 处理相关分类数据（把分类路径的数据也加上）
                    $categorySql = "select path,level from lr_category where id={$dataVal['cat_id']}";
                    $curPath = Yii::$app->db->createCommand($categorySql)->queryOne();
                    if (!empty($curPath['path'])) {
                        $catePathArr = explode(',',$curPath['path']);
                        unset($catePathArr[0],$catePathArr[1],$catePathArr[2]);
                        $catePath = '/'.join('/',$catePathArr).'/';
                        if ($curPath['level'] == 4) {
                            $catePath .= ','.'/'.$catePathArr[3].'/';
                        } elseif ($curPath['level'] == 5) {
                            $catePath .= ','.'/'.$catePathArr[3].'/'.','.'/'.$catePathArr[3].'/'.$catePathArr[4].'/';
                        }
                        $updateArrCategoryId[$dataVal['customer_id']][] = $catePath;
                    } else {
                        $updateArrCategoryId[$dataVal['customer_id']][] = $dataVal['cat_id'];
                    }
                } else {
                    
                    // 处理相关分类数据（把分类路径的数据也加上）
                    $categorySql = "select path,level from lr_category where id={$dataVal['cat_id']}";
                    $curPath = Yii::$app->db->createCommand($categorySql)->queryOne();
                    if (!empty($curPath['path'])) {
                        $catePathArr = explode(',',$curPath['path']);
                        unset($catePathArr[0],$catePathArr[1],$catePathArr[2]);
                        $catePath = '/'.join('/',$catePathArr).'/';
                        if ($curPath['level'] == 4) {
                            $catePath .= ','.'/'.$catePathArr[3].'/';
                        } elseif ($curPath['level'] == 5) {
                            $catePath .= ','.'/'.$catePathArr[3].'/'.','.'/'.$catePathArr[3].'/'.$catePathArr[4].'/';
                        }
                        
                        $insertArrCategoryId[$dataVal['customer_id']][] = $catePath;
                    } else {
                        $insertArrCategoryId[$dataVal['customer_id']][] = '/'.$dataVal['cat_id'].'/';
                    }
                    
                    // 插入数据
                    
                    if (!empty($existUserEmailTemp[$dataVal['customer_id']])) {
                       
                        if (!empty($userCountryBriefCodeArr[trim($dataVal['country_id'])])) {
                            $regionCode = $userCountryBriefCodeArr[trim($dataVal['country_id'])];
                        } else {
                            $regionCode = '';
                        }
                        $insertArr[$dataVal['customer_id']] = "'{$regionCode}',1,'".$existUserEmailTemp[$dataVal['customer_id']]."',"."''".",".$dataVal['customer_id'].",'".$dataVal['sales_time']."',".$dataVal['order_count'].",".$dataVal['order_total_price'];
                    }
                    // else {
                        // $insertArr[$dataVal['customer_id']] = "1,'".$dataVal['customer_email']."',"."''".",".$dataVal['customer_id'].",'".$dataVal['sales_time']."',".$dataVal['order_count'].",".$dataVal['order_total_price'];
                    // }
                    
                    
                }
                
            }
           
            if (!empty($updateArr)) {
                foreach ($updateArr as $updateArrKey=>$updateArrVal) {
                   
                    if (!empty($updateArrCategoryId[$updateArrKey])) {
                        $updateCategoryStr = ",".join(',',$updateArrCategoryId[$updateArrKey]).",";
                    } else {
                        $updateCategoryStr = "";
                    }
                    if (!empty($userCountryBriefCodeArr[trim($updateArrVal['country_id'])])) {
                        $regionCode = $userCountryBriefCodeArr[trim($updateArrVal['country_id'])];
                    } else {
                        $regionCode = '';
                    }
                    $updateSql = "update user_langria set reg_time=1, last_order_time='{$updateArrVal['sales_time']}',order_count={$updateArrVal['order_count']},order_total_price={$updateArrVal['order_total_price']},category_id_str='{$updateCategoryStr}',region_code='{$regionCode}' where site_user_id = {$updateArrKey}";
                   
                    Yii::$app->db->createCommand($updateSql)->execute();
                }
            }
            
            if (!empty($insertArr)) {
                foreach ($insertArr as $insertArrKey=>$insertArrVal) {
                    if (!empty($insertArrCategoryId[$insertArrKey])) {
                        $insertArr[$insertArrKey] .= ",',".join(',',$insertArrCategoryId[$insertArrKey]).",'";
                    } else {
                        $insertArr[$insertArrKey] .= ",''";
                    } 
                }
                $insertSql = $insertPre."(".join("),(",$insertArr).")";               
                Yii::$app->db->createCommand($insertSql)->execute();
            }
        } 
    }
    
    /**
     * 获取LR分类相关数据
     */
    public function actionGetLrCategory()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $apiUrl = [
            'dev' => 'http://pdm.hqygou.com/interface/category/index',
            'test' => 'http://pdm.hqygou.com/interface/category/index',
            'product' => 'http://pdm.gw-ec.com/interface/category/index',
        ];
        
        $token = [
            'dev' => 'fe74b137e9e0ee3a00ddea36c89c59df',
            'test' => 'fe74b137e9e0ee3a00ddea36c89c59df',
            'product' => 'fe74b137e9e0ee3a00ddea36c89c59df',
        ];
        
        $method = yii::$app->request->getMethod();
        $formData = yii::$app->request->$method();
        
        if (!empty($formData['all'])) {
            // 获取所有的分类数据
            $url = $apiUrl[YII_ENV].'?keyid=' . $token[YII_ENV] . '&date=2000-01-01 00:00:00';
        } else {
            // 获取前一天到现在的增量数据
            $curTime =  \yii::$app->bjTime->time();
            $yesTime = $curTime-24*3600;
            $yesDay = \yii::$app->bjTime->date("Y-m-d",$yesTime) . " 00:00:00";
            $url = $apiUrl[YII_ENV].'?keyid=' . $token[YII_ENV] . '&date=' . $yesDay;
        }
        
        // 获取分类数据
        $dataStr = $this->curlGet($url);
        $dataStr = urldecode(ltrim($dataStr,'myjson='));
        $data = json_decode($dataStr,true);
        
        // 数据入库
        // $dataArr = array_chunk($data,5000);
        // foreach ($dataArr as $dataArrVal) {
            
            // foreach () {}
        // }
        $insertPre = "insert into lr_category(id,name_en,name_ch,parent_id,level,path,update_time) values ";
        $insertSql = ""; 
        $insertValArr = [];
        $insertValStr = "";
        foreach ($data as $dataVal) {
            // 查询当前id是否存在，如果存在则更新
            $valId = !empty($dataVal['id']) ? intval($dataVal['id']) : 0;
            if (!$valId) {
                continue;
            }
            $valNameEn = !empty($dataVal['name_en']) ? addslashes($dataVal['name_en']):"";
            $valNameCh = !empty($dataVal['name_ch']) ? addslashes($dataVal['name_ch']):"";
            $valParentId = !empty($dataVal['parent_id']) ? intval($dataVal['parent_id']):0;
            $valLevel = !empty($dataVal['level']) ? intval($dataVal['level']) : 0;
            $valPath = !empty($dataVal['path']) ? addslashes($dataVal['path']):"";
            $valUpdateTime = !empty($dataVal['update_time']) ? addslashes($dataVal['update_time']):"";
            $selectSql = "select count(*) from lr_category where id = ".$valId;

            // 如果当前id有数据则更新
            $dataCount =  Yii::$app->db->createCommand($selectSql)->queryScalar();
            if ($dataCount) {
                $updateSql = "update lr_category set name_en = '{$valNameEn}',name_ch = '{$valNameCh}',parent_id = {$valParentId},level={$valLevel},path='{$valPath}',update_time='{$valUpdateTime}' where id = {$valId}";
                
                Yii::$app->db->createCommand($updateSql)->execute();
                
            } else {    // 不存在则添加到insertVal中
                $insertValArr[] = "{$valId},'{$valNameEn}','{$valNameCh}',{$valParentId},{$valLevel},'{$valPath}','{$valUpdateTime}'";
                
                if (count($insertValArr) >= 5000) {
                    $insertValStr = '('.join('),(',$insertValArr).')';
                    $insertSql = $insertPre.$insertValStr;
                    Yii::$app->db->createCommand($insertSql)->execute();
                    $insertSql = "";
                    $insertValStr = "";
                    $insertValArr = [];
                }
            }
            
        }
        
        if (!empty($insertValArr)){
            $insertValStr = '('.join('),(',$insertValArr).')';
            $insertSql = $insertPre.$insertValStr;
            Yii::$app->db->createCommand($insertSql)->execute();
        }
        
        echo '数据处理结束';
        exit;
    }
    
    /**
     * post 数据到指定URL
     * $post eg : "a=1&c=yy"
     */
    public function curlGet($url){
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
       
       
        $data = curl_exec($ch);
        curl_close($ch);
        return $data;
    }
    // 处理gb的数据
    public function actionDealGbData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $sql = "select count(*) from edm_user_category where is_deal=0"; 
        $dataCount =  Yii::$app->db->createCommand($sql)->queryScalar();
        // 每次处理5000条
        $times = intval(ceil($dataCount / 5000));
        for ($i=1;$i<=$times;$i++) {
            $selectSql = " select * from edm_user_category where is_deal=0 limit 5000";
            $dataInfo = Yii::$app->db->createCommand($selectSql)->queryAll();
            foreach ($dataInfo as $dataVal) {
                $categoryIdStrArr = [];
                $categoryIdStr = "";
                foreach ($this->gbCategoryCh as $gbCategoryChKey => $gbCategoryChVal) {
                    if (!empty($dataVal[$gbCategoryChKey])) {
                        $categoryIdStrArr[] = $gbCategoryChVal;
                    }
                }
                if (!empty($categoryIdStrArr)) {
                    $categoryIdStr = "," . join(',',$categoryIdStrArr) . ',';
                    
                    if (!empty($dataVal['user_id'])) {
                        $updateSql = " update user_gearbest set category_id_str = '{$categoryIdStr}' where site_user_id = {$dataVal['user_id']}";
                       
                        Yii::$app->db->createCommand($updateSql)->execute();
                    }
                }
                
                // 更新
                $updateSql2 = " update edm_user_category set is_deal = 1 where id = {$dataVal['id']}";
                Yii::$app->db->createCommand($updateSql2)->execute();
            }
            sleep(2);
        }
        
    }
    // 处理gb的数据
    public function actionDealGbBakData()
    {
       
        $sql = "select count(*) from edm_user_category";
        $dataCount = Yii::$app->db->createCommand($sql)->queryScalar();
        
        $maxSql = "select max(id) as maxId from edm_user_category";
        $maxId = Yii::$app->db->createCommand($maxSql)->queryOne();
       
        // 每次处理5000条
        $times = intval(ceil($dataCount / 5000));
        
        // 先从redis中查询相关数据
        
        $this->getRedis('gearbest');
        if (!empty($this->redisGroup)) {
            $redisGroup = $this->redisGroup;
            
            $lastQueryMaxId = Yii::$app->$redisGroup->redis->executeCommand('get',['gbQueryMaxId']);
            if (empty($maxId) || $lastQueryMaxId >= $maxId) {
                $lastQueryMaxId = 0;
                Yii::$app->$redisGroup->redis->executeCommand('set',['gbQueryMaxId',0]);
            }
        } else {
            $lastQueryMaxId = 0; // 上次搜索的最大id值
        }
        
        for ($i=1;$i<=$times;$i++) {
            
            $selectSql = " select * from edm_user_category where id > {$lastQueryMaxId} order by id asc limit 5000";
            $dataInfo = Yii::$app->db->createCommand($selectSql)->queryAll();
            if (!empty($dataInfo) && is_array($dataInfo)) {
                $len = count($dataInfo);
                // 入库处理
                foreach ($dataInfo as $dataVal) {
                    
                    
                    // 更新
                    if (!empty($redisGroup)) {
                        Yii::$app->$redisGroup->redis->executeCommand('set',['gbQueryMaxId',$dataInfo[$len-1]['id']]);
                    }
                } 
            } else {
                exit('数据处理完成');
            }
            sleep(1);
        }
    }
}